<?php

namespace Budget\BudgetBundle\Repository;

use Budget\BudgetBundle\Entity\FamilyMonth as FamilyMonthEntity;
use Doctrine\ORM\EntityRepository;
use Budget\BudgetBundle\Entity\Family;
use Budget\BudgetBundle\Entity\Category;
use Budget\BudgetBundle\Helper\DateHelper;
use Budget\BudgetBundle\Helper\FamilyHelper;


class PlannedExpenseRepository extends EntityRepository
{
    public function findByIdForFamily(Family $family, $id)
    {
        $qb = $this->createQueryBuilder('pe')
                   ->select('pe')
                   ->leftJoin('pe.category', 'c')
                   ->where('pe.id = :id')
                   ->andWhere('c.family = :familyId')
                   ->andWhere('pe.date_deleted IS NULL')
                   ->andWhere('c.date_deleted IS NULL')
                   ->setParameter('familyId', $family->getId())
                   ->setParameter('id', $id);

        return $qb->getQuery()->getOneOrNullResult(); 
    }
    
    public function checkActiveExpense(Category $category)
    {     
        $qb = $this->createQueryBuilder('pe')
                   ->select('pe')
                   ->where('pe.category = :category' )
                   ->andWhere('e.date_deleted IS NULL')
                   ->setParameter('category', $category->getId());
        $result = $qb->getQuery()->getArrayResult();
        return count($result);
    }
    
    public function getPlannedExpensesForFamily(Family $family, $date)
    {            
        $date = date(DateHelper::convertToYearMonth($date));
        $qb = $this->createQueryBuilder('pe')
                   ->select('pe', 'c')
                   ->leftJoin('pe.category', 'c')
                   ->where('c.family = :family')
                   ->andWhere('pe.date_deleted IS NULL')
                   ->andWhere('c.date_deleted IS NULL ')
                   ->andWhere('pe.date_created >= :dateFrom ')
                   ->andWhere('pe.date_created <= :dateTo ')
                   ->addOrderBy('pe.name')
                   ->setParameter('family' , $family->getId())
                   ->setParameter('dateFrom' , $date.'-01 '.DateHelper::DAY_START)
                   ->setParameter('dateTo' , $date.'-31 '.DateHelper::DAY_END);
        return  $qb->getQuery()->getResult(); 
    }

    /**
     * @param Family $family
     * @param $date
     * @return array
     * @deprecated
     */
    public function getPlannedExpensesForFamilyToArray(Family $family, $date)
    {             
        //$date = date(DateHelper::convertToYearMonth($date));
        $year  = DateHelper::getYear($date);
        $month = DateHelper::getMonth($date);
        $startDateStr = FamilyHelper::getStartMonthDbStr($family, $year, $month);
        $endDateStr   = FamilyHelper::getEndMonthDbStr($family, $year, $month);
                    
        $qb = $this->createQueryBuilder('pe')
                   ->select('pe', 'c')
                   ->leftJoin('pe.category', 'c')
                   ->where('c.family = :family')
                   ->andWhere('pe.date_deleted IS NULL')
                   ->andWhere('c.date_deleted IS NULL ')
                   ->andWhere('pe.date_expired >= :dateFrom ')
                   ->andWhere('pe.date_expired <= :dateTo ')
                   ->addOrderBy('pe.date_execution')
                   ->setParameter('family' , $family->getId())
                   ->setParameter('dateFrom' , $startDateStr)
                   ->setParameter('dateTo' , $endDateStr);
        //echo $qb->getQuery()->getSQL(); var_dump($startDateStr, $endDateStr);
        return  $qb->getQuery()->getArrayResult();  
    }

    /**
     * Fetch planned expenses for family. Range is defined by family month
     *
     * @param Family $family
     * @param FamilyMonth $familyMonth
     * @throws \Exception
     * @return array
     */
    public function getPlannedExpensesForFamilyByMonth(Family $family, FamilyMonthEntity $familyMonth = null)
    {
        if (is_null($familyMonth)) {
            $familyMonth = $this->getEntityManager()->getRepository('BudgetBudgetBundle:FamilyMonth')->findCurrentForFamily($family);
        }

//        if (!$familyMonth instanceof FamilyMonth) {
//            throw new \Exception('Family month not found!');
//        }


        $qb = $this->createQueryBuilder('pe')
            ->select('pe', 'c')
            ->leftJoin('pe.category', 'c')
            ->where('c.family = :family')
            ->andWhere('pe.date_deleted IS NULL')
            ->andWhere('c.date_deleted IS NULL ')
            ->andWhere('pe.date_expired >= :dateFrom ')
            ->andWhere('pe.date_expired <= :dateTo ')
            ->addOrderBy('pe.date_execution')
            ->setParameter('family' , $family->getId())
            ->setParameter('dateFrom' , $familyMonth->getStart())
            ->setParameter('dateTo' , $familyMonth->getEnd());

        return  $qb->getQuery()->getArrayResult();
    }
    
}
